<?php
defined ( 'BASEPATH' ) or exit ( 'No direct script access allowed' );
class Order_model extends CI_Model {
	public function __construct() {
		parent::__construct ();
	}
	/*
	 * 返回订单数量
	 */
	public function count_orders($state,$phone){
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		if($state!=-1){
			$this->db->where('state', $state);
		}
		if(!empty($phone)){
			$this->db->where('phone', $phone);
		}
		$query = $this->db->get('t_order');
		return $query->num_rows();
	}
	/*
	 * 根据分页信息返回订单信息
	 */
	public function get_orders($state,$phone,$page,$offset){//
		$this->db->select('t_order.id,t_order.orderno,t_order.order_time,t_order.state,t_order.del,t_user.pname,t_user.sname,t_user.phone');
		$this->db->from('t_order');
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		if($state!=-1){
			$this->db->where('state', $state);
		}
		if(!empty($phone)){
			$this->db->where('phone', $phone);
		}
		$this->db->limit($page, $offset);
		$this->db->order_by("id", "desc");
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 根据订单ID返回订单信息
	 */
	public function load($id){//
		$this->db->select('t_order.id,t_order.orderno,t_user.pname,t_user.phone,t_order.order_time,t_order.total,t_order.del');
		$this->db->from('t_order');
		$this->db->where('t_order.id',$id);
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$query = $this->db->get();
		if ($row = $query->row_array()){
			return $row;
		}
		return array();
	}
	/*
	 * 保存订单信息
	 */
	public function create($order) {
		$this->db->insert ( 't_order', $order );
		return $this->db->insert_id();//返回插入的ID
	}
	/*
	 * 删除订单信息
	 */
	public function delete($id){
		$this->db->where('id', $id);
		$this->db->delete('t_order');
		return $this->db->affected_rows()>0;
	}
	/*
	 * 更新订单删除状态
	 */
	public function update($id,$neworder){
		$this->db->where('id', $id);
		$this->db->update('t_order', $neworder);
		return $this->db->affected_rows()>0;
	}
	/*
	 * 通过用户ID返回本周内下的订单
	 */
	public function find_by_user($userid) {
		$this->db->where('t_order.del=0');
		$this->db->where('userid',$userid);
		$this->db->where("YEARWEEK(date_format(order_time,'%Y-%m-%d')) = YEARWEEK(now())");
		$query = $this->db->get('t_order');
		return $query->row_array();
	}
	/*
	 * 通过用户ID返回最新下的有效订单
	 */
	public function find_order_by_user($userid) {
		$this->db->where('state=0');
		$this->db->where('del=0');
		$this->db->where('userid',$userid);
		$this->db->order_by("id", "desc");
		$query = $this->db->get('t_order');
		return $query->row_array();
	}
	/*
	 * 根据订单编号得到订单信息
	 */
	public function find_by_orderno($orderno) {
		$this->db->where('orderno',$orderno);
		$query = $this->db->get('t_order');
		return $query->row_array();
	}
	/*
	 * 一键更新订单信息
	 */
	public function update_orders($state){
		if($state==1){
			$sql="update t_order set state=1 where state=0 and del=0";
		}else if($state==2){
			$sql="update t_order set state=2 where state=1 and del=0";
		}else{
			return false;
		}
	    $this->db->query($sql);
		return $this->db->affected_rows()>0;
	}
	/*
	 * 根据日期返回订单(导出配送单)
	 */
	public function find_by_date($date){
		$this->db->join('t_orderdetail', 't_orderdetail.orderid = t_order.id','left');
		$this->db->where('t_order.del=0');
		$this->db->where('t_orderdetail.date',$date);
		$query = $this->db->get('t_order');
		return $query->result_array();
	}
	/*
	 * 根据日期和班级返回订单(导出配送单)(暂时没用)
	 */
	public function find_by_date_class($date,$class){
		$this->db->select('t_user.sno,t_user.sname,t_product.name,t_orderdetail.amount,t_orderdetail.date');
		$this->db->from('t_order');
		$this->db->where('t_order.del=0');
		$this->db->where('t_orderdetail.date',$date);
		$this->db->where('t_user.class',$class);
		$this->db->where('t_connect.select=0');
		$this->db->join('t_orderdetail', 't_orderdetail.orderid = t_order.id','left');
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$this->db->join('t_product', 't_orderdetail.productid = t_product.id','left');
		$this->db->join('t_connect', 't_connect.productid = t_product.id','left');
		$this->db->order_by("t_user.sno");
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 根据日期和班级返回商品统计(导出配送单)
	 */
	public function count_by_date_class($date,$class){
		$this->db->select('t_product.name,sum(t_orderdetail.amount) as sum');
		$this->db->from('t_order');
		$this->db->where('t_order.del=0');
		$this->db->where('t_orderdetail.date',$date);
		$this->db->where('t_user.class',$class);
		$this->db->group_by("t_orderdetail.productid");
		$this->db->join('t_orderdetail', 't_orderdetail.orderid = t_order.id','left');
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$this->db->join('t_product', 't_orderdetail.productid = t_product.id','left');
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 根据日期和学校返回数量(用于导出订单统计)
	 */
	public function count_by_date_school($date,$school){
		$this->db->join('t_orderdetail', 't_orderdetail.orderid = t_order.id','left');
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$this->db->join('t_class', 't_user.class = t_class.id','left');
		$this->db->where('t_order.del=0');
		$this->db->where('t_orderdetail.date',$date);
		$this->db->where('t_class.schoolid',$school);
		$num = $this->db->count_all_results('t_order');
		return $num;
	}
	/*
	 * 根据日期，类型和学校返回数量(用于导出学生订餐表)
	 */
	public function count_by_date_type_school($date,$type,$school){
		$this->db->join('t_orderdetail', 't_orderdetail.orderid = t_order.id','left');
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$this->db->join('t_class', 't_user.class = t_class.id','left');
		$this->db->where('t_order.del=0');
		$this->db->where('t_orderdetail.date',$date);
		$this->db->where('t_orderdetail.type',$type);
		$this->db->where('t_class.schoolid',$school);
		$num = $this->db->count_all_results('t_order');
		return $num;
	}
	/*
	 * 根据日期，学校和就餐类型返回商品统计(用于导出订单统计)
	 */
	public function sum_by_date_school_type($date,$school,$type){
		$this->db->select('t_product.id,t_product.name,sum(t_orderdetail.amount) as sum,t_orderdetail.type');
		$this->db->from('t_order');
		$this->db->join('t_orderdetail', 't_orderdetail.orderid = t_order.id','left');
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$this->db->join('t_class', 't_user.class = t_class.id','left');
		$this->db->join('t_product', 't_orderdetail.productid = t_product.id','left');
		$this->db->where('t_order.del=0');
		$this->db->where('t_orderdetail.defaults=0');
		$this->db->where('t_orderdetail.date',$date);
		$this->db->where('t_orderdetail.type',$type);
		$this->db->where('t_class.schoolid',$school);
		$this->db->group_by('t_orderdetail.productid');
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 根据日期，学校，就餐类型和年级返回商品统计(用于导出订单统计)
	 */
	public function sum_by_date_school_type_grade($date,$school,$type,$grade,$product){
		$this->db->select('t_product.name,sum(t_orderdetail.amount) as sum');
		$this->db->from('t_order');
		$this->db->join('t_orderdetail', 't_orderdetail.orderid = t_order.id','left');
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$this->db->join('t_class', 't_user.class = t_class.id','left');
		$this->db->join('t_product', 't_orderdetail.productid = t_product.id','left');
		$this->db->where('t_order.del=0');
		$this->db->where('t_orderdetail.date',$date);
		$this->db->where('t_orderdetail.type',$type);
		$this->db->where('t_orderdetail.productid',$product);
		$this->db->where('t_class.schoolid',$school);
		if($grade!=-1){
			$this->db->where('t_class.gradeid',$grade);
		}
		$query = $this->db->get();
		return $query->row_array();
	}
	/*
	 * 根据日期返回商品统计(导出订单统计)
	 */
	public function count_by_date($date){
		$this->db->select('t_product.name,sum(t_orderdetail.amount) as sum');
		$this->db->from('t_order');
		$this->db->where('t_order.del=0');
		$this->db->where('t_orderdetail.date',$date);
		$this->db->group_by("t_orderdetail.productid");
		$this->db->join('t_orderdetail', 't_orderdetail.orderid = t_order.id','left');
		$this->db->join('t_product', 't_orderdetail.productid = t_product.id','left');
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 根据用户id和日期返回商品信息(用于刷卡取餐)
	 */
	public function find_by_user_date($userid,$date,$type){
		$this->db->select('t_orderdetail.id,t_orderdetail.take,t_product.categoryid,t_category.name');
		$this->db->from('t_order');
		$this->db->join('t_orderdetail', 't_order.id = t_orderdetail.orderid','left');
		$this->db->join('t_product', 't_product.id = t_orderdetail.productid','left');
		$this->db->join('t_category', 't_category.id = t_product.categoryid','left');
		$this->db->where('t_order.del=0');
		//$this->db->where('t_order.state=1');
		$this->db->where('t_order.userid',$userid);
		$this->db->where('t_orderdetail.date',$date);
		$this->db->where('t_orderdetail.type',$type);
		$query = $this->db->get();
		return $query->row_array();
	}
	/*
	 * 返回所有有效订单
	 */
	public function find_all_orders(){
		$this->db->where('del=0');
		$query = $this->db->get('t_order');
		return $query->result_array();
	}
	/*
	 * 返回所有无效订单
	 */
	public function find_del_orders(){
		$this->db->where('del=1');
		$query = $this->db->get('t_order');
		return $query->result_array();
	}
	/*
	 * 根据用户id和日期返回商品信息(导出学生订餐表)(新)
	 */
	public function find_by_user_dates($userid,$sdate,$edate){
		$this->db->select('t_orderdetail.id,t_category.name');
		$this->db->from('t_order');
		$this->db->join('t_orderdetail', 't_order.id = t_orderdetail.orderid','left');
		$this->db->join('t_product', 't_product.id = t_orderdetail.productid','left');
		$this->db->join('t_category', 't_category.id = t_product.categoryid','left');
		$this->db->where('t_order.del=0');
		//$this->db->where('t_order.state=1');
		$this->db->where('t_order.userid',$userid);
		$this->db->where('t_orderdetail.date>=',$sdate);
		$this->db->where('t_orderdetail.date<=',$edate);
		$this->db->order_by('t_orderdetail.date,t_orderdetail.type');
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 返回订单信息
	 */
	public function find_by_id($id){
		$this->db->where('id>=', $id);
		$this->db->where('del=0');
		$query = $this->db->get('t_order');
		return $query->result_array();
	}
}